-- =====================================================
-- 04_create_triggers_function.sql
-- 定义函数, 触发器, 和迁移逻辑
-- =====================================================

BEGIN;

-- 1) Audit logging function and triggers for core tables
CREATE OR REPLACE FUNCTION fn_audit_log() RETURNS trigger AS $$
DECLARE
    pk TEXT := NULL;
    sid TEXT := current_setting('app.current_staff_id', true);
BEGIN
    -- try to pick a sensible primary key value based on table name
    IF TG_TABLE_NAME = 'donors' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.donor_id::text ELSE NEW.donor_id::text END) );
    ELSIF TG_TABLE_NAME = 'sperm_vials' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.vial_id::text ELSE NEW.vial_id::text END) );
    ELSIF TG_TABLE_NAME = 'recipients' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.recipient_id::text ELSE NEW.recipient_id::text END) );
    ELSIF TG_TABLE_NAME = 'medical_screenings' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.screening_id::text ELSE NEW.screening_id::text END) );
    ELSIF TG_TABLE_NAME = 'partner_clinics' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.clinic_id::text ELSE NEW.clinic_id::text END) );
    ELSIF TG_TABLE_NAME = 'orders' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.order_id::text ELSE NEW.order_id::text END) );
    ELSIF TG_TABLE_NAME = 'shipments' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.shipment_id::text ELSE NEW.shipment_id::text END) );
    ELSIF TG_TABLE_NAME = 'offspring_tracking' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.offspring_id::text ELSE NEW.offspring_id::text END) );
    ELSIF TG_TABLE_NAME = 'staff' THEN
        pk := COALESCE( (CASE WHEN TG_OP = 'DELETE' THEN OLD.staff_id::text ELSE NEW.staff_id::text END) );
    ELSE
        -- fallback: try to stringify a small subset; keep it short
        IF TG_OP = 'DELETE' THEN
            pk := LEFT(COALESCE(OLD::text, ''), 250);
        ELSE
            pk := LEFT(COALESCE(NEW::text, ''), 250);
        END IF;
    END IF;

    INSERT INTO audit_logs(staff_id, action, table_name, record_id, log_time)
    VALUES (
        CASE WHEN sid IS NULL OR sid = '' THEN NULL ELSE sid::INT END,
        TG_OP || ' on ' || TG_TABLE_NAME,
        TG_TABLE_NAME,
        pk,
        CURRENT_TIMESTAMP
    );

    -- return appropriate record
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- create triggers for core tables (avoid recursively triggering on audit_logs)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_donors') THEN
        CREATE TRIGGER trg_audit_donors AFTER INSERT OR UPDATE OR DELETE ON donors
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_sperm_vials') THEN
        CREATE TRIGGER trg_audit_sperm_vials AFTER INSERT OR UPDATE OR DELETE ON sperm_vials
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_recipients') THEN
        CREATE TRIGGER trg_audit_recipients AFTER INSERT OR UPDATE OR DELETE ON recipients
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_medical_screenings') THEN
        CREATE TRIGGER trg_audit_medical_screenings AFTER INSERT OR UPDATE OR DELETE ON medical_screenings
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_partner_clinics') THEN
        CREATE TRIGGER trg_audit_partner_clinics AFTER INSERT OR UPDATE OR DELETE ON partner_clinics
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_orders') THEN
        CREATE TRIGGER trg_audit_orders AFTER INSERT OR UPDATE OR DELETE ON orders
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_shipments') THEN
        CREATE TRIGGER trg_audit_shipments AFTER INSERT OR UPDATE OR DELETE ON shipments
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_offspring') THEN
        CREATE TRIGGER trg_audit_offspring AFTER INSERT OR UPDATE OR DELETE ON offspring_tracking
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_staff') THEN
        CREATE TRIGGER trg_audit_staff AFTER INSERT OR UPDATE OR DELETE ON staff
        FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
    END IF;
END$$;

-- 2) Reserve / release vial when order_items change
-- (修改逻辑以适应 order_items 表)
CREATE OR REPLACE FUNCTION fn_reserve_vial_on_order_item() RETURNS trigger AS $$
BEGIN
    -- Mark the vial as Reserved when a new order item is created
    UPDATE sperm_vials SET status = 'Reserved' WHERE vial_id = NEW.vial_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_release_vial_on_order_item_delete() RETURNS trigger AS $$
BEGIN
    -- Mark the vial as Available when an order item is deleted (order cancelled)
    UPDATE sperm_vials SET status = 'Available' WHERE vial_id = OLD.vial_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_update_vial_on_order_status() RETURNS trigger AS $$
DECLARE
    v_vial_id INT;
BEGIN
    -- Update vial status based on order status changes
    IF TG_OP = 'UPDATE' THEN
        IF NEW.status = 'Cancelled' THEN
            -- 订单取消：释放样本，但保留order_items记录（保留订单历史）
            FOR v_vial_id IN SELECT vial_id FROM order_items WHERE order_id = NEW.order_id LOOP
                UPDATE sperm_vials SET status = 'Available' WHERE vial_id = v_vial_id;
            END LOOP;
        ELSIF NEW.status = 'Completed' THEN
            -- 订单完成：标记样本为已使用
            FOR v_vial_id IN SELECT vial_id FROM order_items WHERE order_id = NEW.order_id LOOP
                UPDATE sperm_vials SET status = 'Used' WHERE vial_id = v_vial_id;
            END LOOP;
        ELSIF NEW.status = 'Paid' THEN
            -- 订单已支付：保持或设置为预订状态
            FOR v_vial_id IN SELECT vial_id FROM order_items WHERE order_id = NEW.order_id LOOP
                UPDATE sperm_vials SET status = 'Reserved' WHERE vial_id = v_vial_id;
            END LOOP;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach triggers
DO $$
BEGIN
    -- 触发器 1: 当 order_items 中插入新项时，预定 vial
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_reserve_vial_on_order_item_insert') THEN
        CREATE TRIGGER trg_reserve_vial_on_order_item_insert AFTER INSERT ON order_items
        FOR EACH ROW EXECUTE FUNCTION fn_reserve_vial_on_order_item();
    END IF;
    
    -- 触发器 2: 当 order_items 中删除项时 (例如订单取消)，释放 vial
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_release_vial_on_order_item_delete') THEN
        CREATE TRIGGER trg_release_vial_on_order_item_delete AFTER DELETE ON order_items
        FOR EACH ROW EXECUTE FUNCTION fn_release_vial_on_order_item_delete();
    END IF;

    -- 触发器 3: 当 orders 表的状态更新时 (Completed, Cancelled)，更新所有相关 vials 的状态
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_update_vial_on_orders_status') THEN
        CREATE TRIGGER trg_update_vial_on_orders_status AFTER UPDATE OF status ON orders
        FOR EACH ROW EXECUTE FUNCTION fn_update_vial_on_order_status();
    END IF;
END$$;


-- 3) Migrate existing single-vial orders into order_items (from original file)
--    (注意：此迁移脚本在02_insert_sample_data.sql之后运行会失败,
--    因为它假设vial_id在orders表中。
--    在我们的新结构中, 01_create_tables.sql 已经正确地创建了没有vial_id的orders表,
--    所以这个迁移脚本不再需要, 除非你是从一个旧的、未标准化的数据库迁移。)
--    DO $$
--    BEGIN
--        IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='orders' AND column_name='vial_id') THEN
--            INSERT INTO order_items(order_id, vial_id, quantity, price_per_unit)
--            SELECT order_id, vial_id, 1, NULL FROM orders WHERE vial_id IS NOT NULL
--            ON CONFLICT DO NOTHING;
--            ALTER TABLE orders DROP COLUMN IF EXISTS vial_id;
--        END IF;
--    END$$;


-- 4) Offspring limit trigger: block inserts when donor exceeds allowed offspring
CREATE OR REPLACE FUNCTION fn_check_offspring_limit() RETURNS trigger AS $$
DECLARE
    cnt INT;
    max_offspring INT := 25; -- adjust to your regulation
BEGIN
    SELECT COUNT(*) INTO cnt FROM offspring_tracking WHERE donor_id = NEW.donor_id;
    IF cnt >= max_offspring THEN
        RAISE EXCEPTION 'Donor % has reached the maximum allowed offspring (%)', NEW.donor_id, max_offspring;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_check_offspring_limit') THEN
        CREATE TRIGGER trg_check_offspring_limit BEFORE INSERT ON offspring_tracking
        FOR EACH ROW EXECUTE FUNCTION fn_check_offspring_limit();
    END IF;
END$$;

-- 5) Inactive donor protection: prevent reserving vials from inactive donors
CREATE OR REPLACE FUNCTION fn_check_donor_active_on_order_item() RETURNS trigger AS $$
DECLARE
    v_donor_id INT;
    v_is_active BOOLEAN;
BEGIN
    -- Get the donor_id and is_active status for the vial
    SELECT sv.donor_id, d.is_active 
    INTO v_donor_id, v_is_active
    FROM sperm_vials sv
    JOIN donors d ON sv.donor_id = d.donor_id
    WHERE sv.vial_id = NEW.vial_id;
    
    -- Check if donor is active
    IF v_is_active = false THEN
        RAISE EXCEPTION 'Cannot create order item: Donor % is inactive', v_donor_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_check_donor_active_on_order_item') THEN
        CREATE TRIGGER trg_check_donor_active_on_order_item BEFORE INSERT ON order_items
        FOR EACH ROW EXECUTE FUNCTION fn_check_donor_active_on_order_item();
    END IF;
END$$;

-- 6) Auto-deactivate donor when offspring limit reached
CREATE OR REPLACE FUNCTION fn_deactivate_donor_at_limit() RETURNS trigger AS $$
DECLARE
    cnt INT;
    max_offspring INT := 25; -- same as offspring limit
BEGIN
    -- Count offspring after insert
    SELECT COUNT(*) INTO cnt FROM offspring_tracking WHERE donor_id = NEW.donor_id;
    
    -- If reached limit, deactivate the donor
    IF cnt >= max_offspring THEN
        UPDATE donors SET is_active = false WHERE donor_id = NEW.donor_id;
        RAISE NOTICE 'Donor % has been automatically deactivated due to reaching offspring limit (%)', NEW.donor_id, max_offspring;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_deactivate_donor_at_limit') THEN
        CREATE TRIGGER trg_deactivate_donor_at_limit AFTER INSERT ON offspring_tracking
        FOR EACH ROW EXECUTE FUNCTION fn_deactivate_donor_at_limit();
    END IF;
END$$;

COMMIT;